var express = require('express')
var router = express.Router()
const user = require('../utils')
const query = require('../pool')
const IP2Region = require('ip2region').default;
// 创建一个IP2Region实例
const ip2Region = new IP2Region();

//GET 获取 某个用户 或 某些用户 最新坐标位置
router.get('/getLocationByUser', async (req, res) => {
  const [err, data] = await query(`SELECT lr.*
FROM location_record lr
INNER JOIN (
    SELECT lr_inner.user_id, MAX(lr_inner.record_time) as latest_time
    FROM location_record lr_inner
    INNER JOIN user u ON lr_inner.user_id = u.id
    WHERE u.id IN (${req.query.id}) AND u.status != 0
    GROUP BY lr_inner.user_id
) latest ON lr.user_id = latest.user_id AND lr.record_time = latest.latest_time
ORDER BY lr.user_id`);
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      data: data
    })
  }
})

//GET 获取所有人最新坐标位置
router.get('/getLocations', async (req, res) => {
  const [err, data] = await query(`
    SELECT t1.*
    FROM location_record t1
    JOIN (
        SELECT user_id, MAX(record_time) AS max_record_time
        FROM location_record
        GROUP BY user_id
    ) t2 ON t1.user_id = t2.user_id AND t1.record_time = t2.max_record_time;
  `)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      data: data
    })
  }
})

router.post('/sendLocation', async (req, res) => {
  const ip = req.headers['x-forwarded-for'] || req.ip;
  const [err, data] = await query(`INSERT INTO location_record (ip, longitude, latitude, note, user_id) VALUES('${ip}', '${req.body.lon}', '${req.body.lat}', '', '${req.body.userId}');`)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      msg: '添加数据成功!'
    })
  }
})
/** 更新用户在线状态信息 */
router.post('/updateStatus', async (req, res) => {
  const ip = req.headers['x-forwarded-for'] || req.ip;
  const [err, data] = await query(`UPDATE user
    SET status ='${req.body.status}' WHERE id='${req.body.id}'`)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      msg: '添加数据成功!'
    })
  }
})

//GET 获取点赞的最新数据
router.get('/getLikeCount', async (req, res) => {
  const validColumns = ['page_from', 'user_id']; // 定义允许查询的列
  let columnsToSelect = '*';
  const {
    page_from,
    user_id
  } = req.query;
  if (page_from && validColumns.includes('page_from')) {
    columnsToSelect = 'page_from';
  }
  if (user_id && validColumns.includes('user_id')) {
    columnsToSelect = 'user_id';
  }
  const sql = `SELECT COUNT(${columnsToSelect}) AS count FROM HopeTravel.like`;
  const [err, data] = await query(sql)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      data: data[0].count
    })
  }
})

// 点赞
router.post('/sendLike', async (req, res) => {
  const ip = req.headers['x-forwarded-for'] || req.ip;
  const {
    country,
    province,
    city,
    isp
  } = ip2Region.search(ip);
  const page_from = req.body.page_from ? req.body.page_from : ''
  const sql = `INSERT INTO HopeTravel.like
(ip, page_from, country, province, city, isp)
VALUES('${ip}', '${page_from}', '${country}', '${province}', '${city}', '${isp}');`
  let [err, data] = await query(sql)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      data: ''
    })
  }
})

// 点赞
router.post('/sendSee', async (req, res) => {
  const ip = req.headers['x-forwarded-for'] || req.ip;
  const {
    country,
    province,
    city,
    isp
  } = ip2Region.search(ip);
  const page_from = req.body.page_from ? req.body.page_from : ''
  const sql = `INSERT INTO HopeTravel.see
(ip, page_from, country, province, city, isp)
VALUES('${ip}', '${page_from}', '${country}', '${province}', '${city}', '${isp}');`
  let [err, data] = await query(sql)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      data: ''
    })
  }
})

//GET 获取公告
router.get('/getNotices', async (req, res) => {
  const [err, data] = await query(`
    SELECT *
    FROM HopeTravel.notice ORDER BY created_time DESC;
  `)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      data: data
    })
  }
})

//GET 获取地图反馈/建议
router.get('/getAdvice', async (req, res) => {
  const [err, data] = await query(`
    SELECT *
    FROM HopeTravel.advice ORDER BY created_time DESC;
  `)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      data: data
    })
  }
})

// POST 发送地图反馈/建议
router.post('/sendAdvice', async (req, res) => {
  const ip = req.headers['x-forwarded-for'] || req.ip;
  const {
    province
  } = ip2Region.search(ip);
  const content = req.body.content ? req.body.content : ''
  const sql = `INSERT INTO HopeTravel.advice
(province, content)
VALUES('${province.replace(/省/, '')}', '${content}');`
  const [err] = await query(sql)
  if (err) {
    res.send({
      code: -1,
      msg: err
    })
  } else {
    res.send({
      code: 0,
      data: ''
    })
  }
})

module.exports = router
